﻿Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Data
Imports Npgsql

'マスタ編集
Public Class NpgSqlUtilities


#Region "メール一覧"
    'メール一覧画面用
    Public ReadOnly MST As New STC_MeisaiTable("メール一覧", "SELECT RECEIPTTIME,MALWARENAME FROM MAILLIST", _
                                               New Boolean(1) {True, True}, _
                                               New String(1) {"受信時刻", "マルウェア名"}, _
                                               New Integer(1) {150, 400})

    Public MSTWhere As STC_Item() = New STC_Item(1) { _
        New STC_Item("MALWARENAME", "マルウェア名", NpgsqlTypes.NpgsqlDbType.Varchar, 255, "WHERE", "=", Nothing), _
        New STC_Item("RECEIPTTIME", "受信時刻", NpgsqlTypes.NpgsqlDbType.Timestamp, 19, "AND", "=", Nothing)}

#End Region

#Region "メール本文"
    'メール本文画面用
    Public ReadOnly DETAIL As New STC_MeisaiTable("メール本文", "SELECT RECEIPTTIME,MAILIDENTIFYNUM, SECURITYEVENTID, IPADDRESS, COUNTRY, CITY, ORGANIZATION, APPLICATION, DETECTURL, MALWARENAME, TRUSTMALWAREDATE, MALWAREGROUP, USERAGENT, CODE, USERID, CODEKEYID  FROM MAILDETAIL", _
                                               New Boolean(15) {True, True, True, True, True, True, True, True, True, True, True, True, True, True, True, True}, _
                                               New String(15) {"Feed種別", "発生時刻", "セキュリティイベントID", "IPアドレス", "国名",
                                                               "都市名", "組織名", "アプリケーション", "検出URL", "マルウェア名",
                                                               "Trusteerマルウェアデータ", "マルウェアグループ", "ユーザーエージェント",
                                                               "コード", "ユーザーID", "暗号キーID"}, _
                                               New Integer(15) {150, 400, 400, 400, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100})

    Public DETAILWhere As STC_Item() = New STC_Item(1) { _
        New STC_Item("RECEIPTTIME", "受信時刻", NpgsqlTypes.NpgsqlDbType.Timestamp, 19, "WHERE", "=", Nothing), _
        New STC_Item("MAILIDENTIFYNUM", "メール識別番号", NpgsqlTypes.NpgsqlDbType.Varchar, 20, "AND", "=", Nothing)
        }
#End Region





    Public Structure STC_MeisaiTable
        'テーブル名
        Private m_tableName As String
        'SQL文
        Private m_sql As String
        '列を表示するかどうか示す
        Private m_visible As Boolean()
        '列のタイトル
        Private m_title As String()
        '列の幅
        Private m_width As Integer()

        Public Sub New(ByVal _tableName As String, ByVal _sql As String, ByVal _visible As Boolean(), ByVal _title As String(), ByVal _width As Integer())
            Me.m_tableName = _tableName
            Me.m_sql = _sql
            Me.m_visible = _visible
            Me.m_title = _title
            Me.m_width = _width
        End Sub

        Public Property TableName() As String
            Get
                Return m_tableName
            End Get
            Set(ByVal value As String)
                m_tableName = value
            End Set
        End Property

        Public Property Sql() As String
            Get
                Return m_sql
            End Get
            Set(ByVal value As String)
                m_sql = value
            End Set
        End Property

        '列を表示するかどうか示す
        Public Property Visible() As Boolean()
            Get
                Return m_visible
            End Get
            Set(ByVal value As Boolean())
                m_visible = value
            End Set
        End Property
        '列のタイトル
        Public Property Title() As String()
            Get
                Return m_title
            End Get
            Set(ByVal value As String())
                m_title = value
            End Set
        End Property
        '列の幅
        Public Property Width() As Integer()
            Get
                Return m_width
            End Get
            Set(ByVal value As Integer())
                m_width = value
            End Set
        End Property
    End Structure

    Public Structure STC_Item
        '列ID
        Private m_itemId As String
        '列名
        Private m_itemName As String
        '列のデータ型
        Private m_type As NpgsqlTypes.NpgsqlDbType
        '列のサイズ
        Private m_size As Integer

        '値とのオペレータ
        Private m_operat As String
        'パラメーターとのオペレータ
        Private m_sqlKey As String

        'パラメーター内容
        Private m_paramValue As Object

        Public Sub New(ByVal _itemId As String, ByVal _itemName As String, ByVal _type As NpgsqlTypes.NpgsqlDbType, ByVal _size As Integer, ByVal _sqlKey As String, ByVal _operat As String, _
         ByVal _paramValue As Object)
            Me.m_itemId = _itemId
            Me.m_itemName = _itemName
            Me.m_type = _type
            Me.m_size = _size
            Me.m_operat = _operat
            Me.m_sqlKey = _sqlKey
            Me.m_paramValue = _paramValue
        End Sub

        '列ID
        Public Property ItemId() As String
            Get
                Return " " & m_itemId & Operat & "@" & m_itemId.ToUpper()
            End Get
            Set(ByVal value As String)
                m_itemId = value
            End Set
        End Property

        '列名
        Public Property ItemName() As String
            Get
                Return m_itemName
            End Get
            Set(ByVal value As String)
                m_itemName = value
            End Set
        End Property
        '列のデータ型
        Public Property Type() As NpgsqlTypes.NpgsqlDbType
            Get
                Return m_type
            End Get
            Set(ByVal value As NpgsqlTypes.NpgsqlDbType)
                m_type = value
            End Set
        End Property
        '列のサイズ
        Public Property Size() As Integer
            Get
                Return m_size
            End Get
            Set(ByVal value As Integer)
                m_size = value
            End Set
        End Property

        '値とのオペレータ
        Public Property Operat() As String
            Get
                Return " " & m_operat & " "
            End Get
            Set(ByVal value As String)
                m_operat = value
            End Set
        End Property

        'パラメーターとのオペレータ
        Public Property SqlKey() As String
            Get
                Return " " & m_sqlKey & " "
            End Get
            Set(ByVal value As String)
                m_sqlKey = value
            End Set
        End Property

        'パラメーターID
        Public ReadOnly Property ParamId() As String
            Get
                Return "@" & m_itemId.ToUpper()
            End Get
        End Property

        'パラメーター内容
        Public Property ParamValue() As Object
            Get
                Return m_paramValue
            End Get
            Set(ByVal value As Object)
                m_paramValue = value
            End Set
        End Property
    End Structure

    'SQL文
    Private Const SQL_LIKE_A As String = "'%'+####+'%'"
    'LIKE ALL
    Private Const SQL_LIKE_S As String = "'%'+####"
    'LIKE START
    Private Const SQL_LIKE_E As String = "####+'%'"
    'LIKE END



    Public Function GetParam(ByVal arrItem As STC_Item()) As NpgsqlParameter()
        Dim cnt As Integer = arrItem.Length
        Dim param As NpgsqlParameter() = New NpgsqlParameter(cnt - 1) {}
        Dim clsSQL As New NpgSqlHelper()
        For i As Integer = 0 To cnt - 1
            param.SetValue(clsSQL.CreateInParam(arrItem(i).ParamId, arrItem(i).ParamValue, arrItem(i).Type, arrItem(i).Size), i)
        Next

        Return param
    End Function
End Class
